{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fourth-economy",
   "metadata": {},
   "source": [
    "# Generator of sql fixtures for data testing\n",
    "\n",
    "This notebook adds a methodology to generate data for doing api testing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "juvenile-amateur",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "opposite-concept",
   "metadata": {},
   "source": [
    "# WDPA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "recent-share",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>wdpaid</th>\n",
       "      <th>the_geom</th>\n",
       "      <th>full_name</th>\n",
       "      <th>iucn_cat</th>\n",
       "      <th>shape_leng</th>\n",
       "      <th>shape_area</th>\n",
       "      <th>iso3</th>\n",
       "      <th>status</th>\n",
       "      <th>desig</th>\n",
       "      <th>created_at</th>\n",
       "      <th>created_by</th>\n",
       "      <th>last_modified_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6d5a8d01-1f65-4c7d-92f8-f60a7712fd65</td>\n",
       "      <td>555577555</td>\n",
       "      <td>0106000020E6100000010000000103000000010000002C...</td>\n",
       "      <td>null</td>\n",
       "      <td>Not Applicable</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>BWA</td>\n",
       "      <td>Inscribed</td>\n",
       "      <td>World Heritage Site (natural or mixed)</td>\n",
       "      <td>2021-03-01 12:55:02.629566</td>\n",
       "      <td>cf37a9bc-6e7b-4207-ac0c-55dd118ba199</td>\n",
       "      <td>2021-03-01 12:55:02.629566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>938d12e7-a49f-468c-ba74-84638d10c87c</td>\n",
       "      <td>555577555</td>\n",
       "      <td>0106000020E6100000010000000103000000010000002C...</td>\n",
       "      <td>null</td>\n",
       "      <td>Not Applicable</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>BWA</td>\n",
       "      <td>Inscribed</td>\n",
       "      <td>World Heritage Site (natural or mixed)</td>\n",
       "      <td>2021-03-01 12:54:49.814692</td>\n",
       "      <td>cf37a9bc-6e7b-4207-ac0c-55dd118ba199</td>\n",
       "      <td>2021-03-01 12:54:49.814692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>856ef5ca-726d-4e3f-8f08-0e7238dabe39</td>\n",
       "      <td>555577555</td>\n",
       "      <td>0106000020E6100000010000000103000000010000002C...</td>\n",
       "      <td>null</td>\n",
       "      <td>Not Applicable</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>BWA</td>\n",
       "      <td>Inscribed</td>\n",
       "      <td>World Heritage Site (natural or mixed)</td>\n",
       "      <td>2021-03-01 12:54:59.646387</td>\n",
       "      <td>cf37a9bc-6e7b-4207-ac0c-55dd118ba199</td>\n",
       "      <td>2021-03-01 12:54:59.646387</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id     wdpaid  \\\n",
       "0  6d5a8d01-1f65-4c7d-92f8-f60a7712fd65  555577555   \n",
       "1  938d12e7-a49f-468c-ba74-84638d10c87c  555577555   \n",
       "2  856ef5ca-726d-4e3f-8f08-0e7238dabe39  555577555   \n",
       "\n",
       "                                            the_geom full_name  \\\n",
       "0  0106000020E6100000010000000103000000010000002C...      null   \n",
       "1  0106000020E6100000010000000103000000010000002C...      null   \n",
       "2  0106000020E6100000010000000103000000010000002C...      null   \n",
       "\n",
       "         iucn_cat shape_leng shape_area iso3     status  \\\n",
       "0  Not Applicable       null       null  BWA  Inscribed   \n",
       "1  Not Applicable       null       null  BWA  Inscribed   \n",
       "2  Not Applicable       null       null  BWA  Inscribed   \n",
       "\n",
       "                                    desig                  created_at  \\\n",
       "0  World Heritage Site (natural or mixed)  2021-03-01 12:55:02.629566   \n",
       "1  World Heritage Site (natural or mixed)  2021-03-01 12:54:49.814692   \n",
       "2  World Heritage Site (natural or mixed)  2021-03-01 12:54:59.646387   \n",
       "\n",
       "                             created_by            last_modified_at  \n",
       "0  cf37a9bc-6e7b-4207-ac0c-55dd118ba199  2021-03-01 12:55:02.629566  \n",
       "1  cf37a9bc-6e7b-4207-ac0c-55dd118ba199  2021-03-01 12:54:49.814692  \n",
       "2  cf37a9bc-6e7b-4207-ac0c-55dd118ba199  2021-03-01 12:54:59.646387  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wdpa = pd.read_csv('/home/jovyan/work/datasets/processed/geo_wdpa_okavango.csv').fillna('null')\n",
    "wdpa.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "rotary-means",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['wdpaid', 'the_geom', 'full_name', 'iucn_cat', 'shape_leng', 'shape_area', 'iso3', 'status', 'desig', 'created_by']\n",
      "['wdpaid', 'the_geom', 'full_name', 'iucn_cat', 'shape_leng', 'shape_area', 'iso3', 'status', 'desig']\n"
     ]
    }
   ],
   "source": [
    "columns = list(filter(lambda x: x not in ['id', 'created_at', 'last_modified_at'], wdpa.columns.values))\n",
    "columnsForData = list(filter(lambda x: x not in ['id', 'created_at', 'last_modified_at', 'created_by'], \n",
    "                             wdpa.columns.values))\n",
    "print(columns)\n",
    "print(columnsForData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "lightweight-protein",
   "metadata": {},
   "outputs": [],
   "source": [
    "text=[]\n",
    "for id, row in wdpa[columnsForData].iterrows():\n",
    "    dataFortext = \"({rowData[0]}, {rowData[1]}::geometry, {rowData[2]}, {rowData[3]}, {rowData[4]}, {rowData[5]}, {rowData[6]}, {rowData[7]}, {rowData[8]}, '$user')\".format(rowData = ['null' if x == 'null' else f\"'{x}'\" if type(x) == str else str(x) for x in row.values])\n",
    "    text.append(dataFortext)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "caring-canada",
   "metadata": {},
   "outputs": [],
   "source": [
    "insertQuery=f'''\n",
    "INSERT INTO wdpa \n",
    "({', '.join(columns)})\n",
    "VALUES\n",
    "{', '.join(text)};\n",
    "'''\n",
    "with open(f\"/home/jovyan/work/datasets/processed/test-wdpa-data.sql\", \"w\")as f:\n",
    "    f.write(insertQuery)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "graphic-arbor",
   "metadata": {},
   "source": [
    "# Admin Regions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ready-sequence",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>the_geom</th>\n",
       "      <th>name_0</th>\n",
       "      <th>name_1</th>\n",
       "      <th>name_2</th>\n",
       "      <th>iso3</th>\n",
       "      <th>gid_0</th>\n",
       "      <th>gid_1</th>\n",
       "      <th>gid_2</th>\n",
       "      <th>level</th>\n",
       "      <th>created_at</th>\n",
       "      <th>created_by</th>\n",
       "      <th>last_modified_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>93bfd2a9-8c12-40c7-ba9d-404a8cff68fe</td>\n",
       "      <td>0106000020E610000008000000010300000001000000A9...</td>\n",
       "      <td>Angola</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>AGO</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>country</td>\n",
       "      <td>2021-03-01 12:42:01.891632</td>\n",
       "      <td>cf37a9bc-6e7b-4207-ac0c-55dd118ba199</td>\n",
       "      <td>2021-03-01 12:42:01.891632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>91c13a0b-ee21-4f51-953e-82ec226338f4</td>\n",
       "      <td>0106000020E61000000100000001030000000100000099...</td>\n",
       "      <td>Botswana</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>BWA</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>country</td>\n",
       "      <td>2021-03-01 12:42:01.891632</td>\n",
       "      <td>cf37a9bc-6e7b-4207-ac0c-55dd118ba199</td>\n",
       "      <td>2021-03-01 12:42:01.891632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29a9c053-d3ed-4a2a-863b-9259590142c7</td>\n",
       "      <td>0106000020E610000003000000010300000001000000D6...</td>\n",
       "      <td>Democratic Republic of the Congo</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>COD</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>country</td>\n",
       "      <td>2021-03-01 12:42:01.891632</td>\n",
       "      <td>cf37a9bc-6e7b-4207-ac0c-55dd118ba199</td>\n",
       "      <td>2021-03-01 12:42:01.891632</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id  \\\n",
       "0  93bfd2a9-8c12-40c7-ba9d-404a8cff68fe   \n",
       "1  91c13a0b-ee21-4f51-953e-82ec226338f4   \n",
       "2  29a9c053-d3ed-4a2a-863b-9259590142c7   \n",
       "\n",
       "                                            the_geom  \\\n",
       "0  0106000020E610000008000000010300000001000000A9...   \n",
       "1  0106000020E61000000100000001030000000100000099...   \n",
       "2  0106000020E610000003000000010300000001000000D6...   \n",
       "\n",
       "                             name_0 name_1 name_2  iso3 gid_0 gid_1 gid_2  \\\n",
       "0                            Angola   null   null  null   AGO  null  null   \n",
       "1                          Botswana   null   null  null   BWA  null  null   \n",
       "2  Democratic Republic of the Congo   null   null  null   COD  null  null   \n",
       "\n",
       "     level                  created_at                            created_by  \\\n",
       "0  country  2021-03-01 12:42:01.891632  cf37a9bc-6e7b-4207-ac0c-55dd118ba199   \n",
       "1  country  2021-03-01 12:42:01.891632  cf37a9bc-6e7b-4207-ac0c-55dd118ba199   \n",
       "2  country  2021-03-01 12:42:01.891632  cf37a9bc-6e7b-4207-ac0c-55dd118ba199   \n",
       "\n",
       "             last_modified_at  \n",
       "0  2021-03-01 12:42:01.891632  \n",
       "1  2021-03-01 12:42:01.891632  \n",
       "2  2021-03-01 12:42:01.891632  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "admin_regions = pd.read_csv('/home/jovyan/work/datasets/processed/geo_admin_regions_okavango.csv').fillna('null')\n",
    "admin_regions.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "judicial-assault",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['the_geom', 'name_0', 'name_1', 'name_2', 'iso3', 'gid_0', 'gid_1', 'gid_2', 'level', 'created_by']\n",
      "['the_geom', 'name_0', 'name_1', 'name_2', 'iso3', 'gid_0', 'gid_1', 'gid_2', 'level']\n"
     ]
    }
   ],
   "source": [
    "columns = list(filter(lambda x: x not in ['id', 'created_at', 'last_modified_at'], admin_regions.columns.values))\n",
    "columnsForData = list(filter(lambda x: x not in ['id', 'created_at', 'last_modified_at', 'created_by'], \n",
    "                             admin_regions.columns.values))\n",
    "print(columns)\n",
    "print(columnsForData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "executive-rough",
   "metadata": {},
   "outputs": [],
   "source": [
    "text=[]\n",
    "for id, row in admin_regions[columnsForData].iterrows():\n",
    "    dataFortext = \"({rowData[0]}::geometry, {rowData[1]}, {rowData[2]}, {rowData[3]}, {rowData[4]}, {rowData[5]}, {rowData[6]}, {rowData[7]}, {rowData[8]}, '$user')\".format(rowData = ['null' if x == 'null' else f\"'{x}'\" if type(x) == str else str(x) for x in row.values])\n",
    "    text.append(dataFortext)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "federal-roads",
   "metadata": {},
   "outputs": [],
   "source": [
    "insertQuery=f'''\n",
    "INSERT INTO admin_regions \n",
    "({', '.join(columns)})\n",
    "VALUES\n",
    "{', '.join(text)};\n",
    "'''\n",
    "with open(f\"/home/jovyan/work/datasets/processed/test-admin-data.sql\", \"w\")as f:\n",
    "    f.write(insertQuery)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "martial-catch",
   "metadata": {},
   "source": [
    "## Features"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "equivalent-passion",
   "metadata": {},
   "source": [
    "### Meta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "gentle-defeat",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>feature_class_name</th>\n",
       "      <th>alias</th>\n",
       "      <th>description</th>\n",
       "      <th>property_name</th>\n",
       "      <th>intersection</th>\n",
       "      <th>tag</th>\n",
       "      <th>creation_status</th>\n",
       "      <th>created_at</th>\n",
       "      <th>created_by</th>\n",
       "      <th>last_modified_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2367e7fd-6476-42cd-96af-380fe79ccda8</td>\n",
       "      <td>iucn_abditomyslatidens</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>binomial</td>\n",
       "      <td>null</td>\n",
       "      <td>species</td>\n",
       "      <td>created</td>\n",
       "      <td>2021-03-01 13:10:00.340666</td>\n",
       "      <td>cf37a9bc-6e7b-4207-ac0c-55dd118ba199</td>\n",
       "      <td>2021-03-01 13:10:00.340666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>e09edcc4-6177-4d5c-8b56-3153e329a856</td>\n",
       "      <td>iucn_abeomelomyssevia</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>binomial</td>\n",
       "      <td>null</td>\n",
       "      <td>species</td>\n",
       "      <td>created</td>\n",
       "      <td>2021-03-01 13:10:00.511928</td>\n",
       "      <td>cf37a9bc-6e7b-4207-ac0c-55dd118ba199</td>\n",
       "      <td>2021-03-01 13:10:00.511928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>665ec258-d329-48fd-9cfd-ed35c315dad6</td>\n",
       "      <td>iucn_abrawayaomyschebezi</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>binomial</td>\n",
       "      <td>null</td>\n",
       "      <td>species</td>\n",
       "      <td>created</td>\n",
       "      <td>2021-03-01 13:10:00.670676</td>\n",
       "      <td>cf37a9bc-6e7b-4207-ac0c-55dd118ba199</td>\n",
       "      <td>2021-03-01 13:10:00.670676</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id        feature_class_name alias  \\\n",
       "0  2367e7fd-6476-42cd-96af-380fe79ccda8    iucn_abditomyslatidens  null   \n",
       "1  e09edcc4-6177-4d5c-8b56-3153e329a856     iucn_abeomelomyssevia  null   \n",
       "2  665ec258-d329-48fd-9cfd-ed35c315dad6  iucn_abrawayaomyschebezi  null   \n",
       "\n",
       "  description property_name intersection      tag creation_status  \\\n",
       "0        null      binomial         null  species         created   \n",
       "1        null      binomial         null  species         created   \n",
       "2        null      binomial         null  species         created   \n",
       "\n",
       "                   created_at                            created_by  \\\n",
       "0  2021-03-01 13:10:00.340666  cf37a9bc-6e7b-4207-ac0c-55dd118ba199   \n",
       "1  2021-03-01 13:10:00.511928  cf37a9bc-6e7b-4207-ac0c-55dd118ba199   \n",
       "2  2021-03-01 13:10:00.670676  cf37a9bc-6e7b-4207-ac0c-55dd118ba199   \n",
       "\n",
       "             last_modified_at  \n",
       "0  2021-03-01 13:10:00.340666  \n",
       "1  2021-03-01 13:10:00.511928  \n",
       "2  2021-03-01 13:10:00.670676  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "api_features = pd.read_csv('/home/jovyan/work/datasets/processed/api_features_okavango.csv').fillna('null')\n",
    "features_data = pd.read_csv('/home/jovyan/work/datasets/processed/geo_features_data_okavango.csv').fillna('null')\n",
    "api_features.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "earlier-lithuania",
   "metadata": {},
   "outputs": [],
   "source": [
    "_filter = features_data.feature_id.unique()[:9]\n",
    "filtered_api_features = api_features[api_features.id.isin(_filter)]\n",
    "filtered_data_features = features_data[features_data.feature_id.isin(_filter)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "advised-talent",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['feature_class_name', 'alias', 'description', 'property_name', 'intersection', 'tag', 'creation_status', 'created_by']\n",
      "['feature_class_name', 'alias', 'description', 'property_name', 'intersection', 'tag', 'creation_status']\n"
     ]
    }
   ],
   "source": [
    "columns = list(filter(lambda x: x not in ['id', 'created_at', 'last_modified_at'], api_features.columns.values))\n",
    "columnsForData = list(filter(lambda x: x not in ['id', 'created_at', 'last_modified_at', 'created_by'], api_features.columns.values))\n",
    "print(columns)\n",
    "print(columnsForData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "aware-sport",
   "metadata": {},
   "outputs": [],
   "source": [
    "text = []\n",
    "for id, row in filtered_api_features[columnsForData].iterrows():\n",
    "    dataFortext = \"({rowData[0]}, {rowData[1]}, {rowData[2]}, {rowData[3]}, {rowData[4]}, {rowData[5]}, {rowData[6]}, (SELECT id FROM users WHERE email = 'aa@example.com'))\".format(rowData = ['null' if x == 'null' else f\"'{x}'\" if type(x) == str else str(x) for x in row.values])\n",
    "    text.append(dataFortext)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "controlled-asthma",
   "metadata": {},
   "outputs": [],
   "source": [
    "insertQuery=f'''\n",
    "INSERT INTO features \n",
    "({', '.join(columns)})\n",
    "VALUES\n",
    "{', '.join(text)};\n",
    "'''\n",
    "with open(f\"/home/jovyan/work/datasets/processed/test-features.sql\", \"w\")as f:\n",
    "    f.write(insertQuery)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "overall-taylor",
   "metadata": {},
   "source": [
    "### Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "stuffed-tuner",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['the_geom', 'properties', 'source', 'feature_id']\n",
      "['the_geom', 'properties', 'source']\n"
     ]
    }
   ],
   "source": [
    "columns = list(filter(lambda x: x not in ['id'], features_data.columns.values))\n",
    "columnsForData = list(filter(lambda x: x not in ['id', 'created_at', 'last_modified_at', 'feature_id'], \n",
    "                             features_data.columns.values))\n",
    "print(columns)\n",
    "print(columnsForData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "moral-lawsuit",
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.exists('/home/jovyan/work/datasets/processed/features'):\n",
    "    os.makedirs('/home/jovyan/work/datasets/processed/features')\n",
    "for posx, feature_row in filtered_api_features.iterrows():\n",
    "    text=[]\n",
    "    for id, row in filtered_data_features[filtered_data_features.feature_id.isin([feature_row.id])][columnsForData].iterrows():\n",
    "        dataFortext = \"({rowData[0]}::geometry, {rowData[1]}::jsonb, {rowData[2]}, '$feature_id')\".format(rowData = ['null' if x == 'null' else f\"'{x}'\" if type(x) == str else str(x) for x in row.values])\n",
    "        text.append(dataFortext)\n",
    "    insertQuery=f'''\n",
    "    INSERT INTO features_data \n",
    "    ({', '.join(columns)})\n",
    "    VALUES\n",
    "    {', '.join(text)};\n",
    "    '''\n",
    "    with open(f\"/home/jovyan/work/datasets/processed/features/{feature_row.feature_class_name}.sql\", \"w\")as f:\n",
    "        f.write(insertQuery)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "alleged-april",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}